package com.Action;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jdom.Attribute;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

public class CreateTemplate {
//创建模板文件
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//获取解析XML文件路径
          String path = System.getProperty("user.dir")+"/src/student.xml";
          System.out.println(path);
          File  file = new File(path);
          SAXBuilder builder = new SAXBuilder();
          
          try {
        	  //解析XML文件
			Document parse = builder.build(file);
			//创建一个EXCEL
			HSSFWorkbook wb =new HSSFWorkbook();
			//创建SHEET
			HSSFSheet sheet = wb.createSheet("Sheet0");
			//获取XML文件根节点(<excel></excel>)
			Element root = parse.getRootElement();
			//获取模板名称
			String template = root.getAttribute("name").getValue();
			
			int rownum=0;
			int column = 0;
			//设置列宽
			Element colgroup = root.getChild("colgroup");
			setColumnWidth(sheet,colgroup);
			//设置标题
			Element title =root.getChild("title");
			List<Element> trs = title.getChildren("tr");
			for(int i =0;i<trs.size();i++) {
				Element tr =trs.get(i);
				List<Element> tds = tr.getChildren("td");
				HSSFRow row = sheet.createRow(rownum);
				for(column=0;column<tds.size();column++) {//<? <=?
					Element td = tds.get(column);
					HSSFCell cell = row.createCell(column);
					Attribute rowSpan = td.getAttribute("rowspan");
					Attribute colSpan = td.getAttribute("colspan");
					Attribute value = td.getAttribute("value");
					if(value!=null) {
						String val = value.getValue();
						cell.setCellValue(val);
						int rspan =rowSpan.getIntValue()-1;
						int cspan =colSpan.getIntValue()-1;
						//合并单元格居中
						sheet.addMergedRegion(new CellRangeAddress(rspan,rspan,0,cspan));
						
					}
				}
				rownum ++;
			}
			//设置表头
			Element thead = root.getChild("thead");
			trs = thead.getChildren("tr");//返回一个列表将所有满足要求的标签都返回
			for(int i = 0;i<trs.size();i++) {
				Element tr = trs.get(i);
				HSSFRow row = sheet.createRow(rownum);
				List<Element> ths= tr.getChildren("th");//获取所有子节点中的名字为TH的属性
				for(column=0;column<ths.size();column++) {
					Element th = ths.get(column);
					Attribute valueAttr = th.getAttribute("value");
					HSSFCell cell = row.createCell(column);
					if(valueAttr!=null) {
						String value = valueAttr.getValue();
						cell.setCellValue(value);
					}
				}
			rownum++;
			}
			//设置数据区域样式
			Element tbody = root.getChild("tbody");
			Element tr = tbody.getChild("tr");
			int repeat = tr.getAttribute("repeat").getIntValue();
			
			List<Element>tds = tr.getChildren("td");
			for(int i = 0;i<repeat;i++) {
				HSSFRow row = sheet.createRow(rownum);
				for(column=0;column<tds.size();column++) {
					Element td = tds.get(column);
					HSSFCell cell = row.createCell(column);
					setType(wb,cell,td);
				}
				rownum++;
			}
			//生成EXCEL文件保存到本地
			//生成EXCEL导入模板
			File tempFile = new File("/Users/duyifan/Desktop/"+template+".xls");
			tempFile.delete();
			tempFile.createNewFile();
			FileOutputStream stream = FileUtils.openOutputStream(tempFile);
			wb.write(stream);
			stream.close();
			
			
		} catch (JDOMException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
	// TODO Auto-generated method stub设置单元格样式
	Attribute typeAttr = td.getAttribute("type");
	String type = typeAttr.getValue();
	HSSFDataFormat format = wb.createDataFormat();
	HSSFCellStyle cellStyle = wb.createCellStyle();
	if("NUMERIC".equalsIgnoreCase(type)) {//比较两个字符串的形式忽略大小写的区别
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		Attribute formatAttr = td.getAttribute("format");
		String formatValue = formatAttr.getValue();
		formatValue=StringUtils.isNotBlank(formatValue)?formatValue:"#,##0.00";
		cellStyle.setDataFormat(format.getFormat(formatValue));
	}else if("STRING".equalsIgnoreCase(type)) {
		cell.setCellValue("");
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cellStyle.setDataFormat(format.getFormat("@"));//设置为文本格式
	}else if("DATE".equalsIgnoreCase(type)) {
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
	}else if("ENUM".equalsIgnoreCase(type)) {
		CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(),cell.getRowIndex(),cell.getColumnIndex(),cell.getColumnIndex());
		Attribute enumAttr = td.getAttribute("format");
		String enumValue = enumAttr.getValue();
		//加载下拉列表内容
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
		//数据有效性对象
		HSSFDataValidation dataValidation = new HSSFDataValidation(regions,constraint);
		wb.getSheetAt(0).addValidationData(dataValidation);
	}
	cell.setCellStyle(cellStyle);
}

private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
	// TODO Auto-generated method stub
	List<Element> cols = colgroup.getChildren("col");
	for(int i = 0;i<cols.size();i++) {
		Element col = cols.get(i);
		Attribute width = col.getAttribute("width");
		String unit = width.getValue().replaceAll("[0-9,\\.]","");
		String value = width .getValue().replaceAll(unit, "");
		int v=0;
		if(StringUtils.isBlank(unit)||"px".endsWith(unit)) {
			v= Math.round(Float.parseFloat(value)*37F);
		}else if("em".endsWith(unit)){
			v= Math.round(Float.parseFloat(value)*267.5F);
		}
		sheet.setColumnWidth(i, v);
	}
	
}

}
